﻿using System;
using System.Linq;
using System.Collections.Generic;
using System.Text;
using System.Data;
using CIT.WMS.Common;
using CIT.WMS.EnumType;

namespace CIT.WMS.Msd
{
    public class MSDService
    {
        static bool flag = true;
        static bool result = true;
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="strwhere"></param>
        /// <returns></returns>
        public static DataTable Query(string strwhere)
        {
            string sqlcmd = string.Format(@"
SELECT  a.SerialNumber ,
        b.IsMSD ,
		c.ExposeTime,
		b.SecondMaterialClass,
        a.MaterialCode ,
        c.Class ,
		c.RoastTime,
        c.SplitTime,
		c.OrderNum,
		c.TemperatureMaxTime,
		c.TemperatureMinTime,
		c.RoastMaxTemperature,
		c.RoastMinTemperature,
		c.ExposeTime,
        d.OpenNum,
		d.[Status],
        a.Lock_Flag,
		a.QTY,
		e.*
FROM    dbo.T_Bllb_StockInfo_tbsi AS a
        LEFT JOIN dbo.MdcdatMaterial AS b ON a.MaterialCode = b.MaterialCode
        LEFT JOIN dbo.T_Bllb_SecondClass_tbsc AS c ON c.Class = b.SecondMaterialClass
        LEFT JOIN dbo.T_Bllb_MSDMain_tbmm AS d ON d.SerialNumber = a.SerialNumber
		LEFT JOIN dbo.T_Bllb_MSDResult_tbmr AS e ON e.SerialNumber=d.SerialNumber
		WHERE 1=1 {0}", strwhere);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 保存拆封记录
        /// </summary>
        /// <param name="MSDMain_tbmm"></param>
        /// <returns></returns>
        public static bool SaveOpenRecords(T_Bllb_MSDMain_tbmm MSDMain_tbmm)
        {
            string sqlcmd = string.Format(@"
IF NOT EXISTS ( SELECT  *
                FROM    dbo.T_Bllb_MSDMain_tbmm
                WHERE   SerialNumber = '{0}' )
    BEGIN
        INSERT  dbo.T_Bllb_MSDMain_tbmm
                ( SerialNumber ,
                  Status ,
                  MaterialCode ,
                  OpenNum ,
                  Remark
		        )
        VALUES  ( N'{0}' , -- SerialNumber - nvarchar(50)
                  N'{1}' , -- Status - nvarchar(50)
                  N'{2}' , -- MaterialCode - nvarchar(50)
                  N'1' , -- OpenNum - nvarchar(50)
                  N'{3}'  -- Remark - nvarchar(50)
		        )
    END
ELSE
    BEGIN
        UPDATE  dbo.T_Bllb_MSDMain_tbmm
        SET     Status = '{1}' ,
                OpenNum = CAST(OpenNum AS INT) + 1 ,
                MaterialCode = '{2}'
        WHERE   SerialNumber = '{0}' 
    END
IF NOT EXISTS ( SELECT  *
                FROM    T_Bllb_MSDResult_tbmr
                WHERE   SerialNumber = '{0}' )
    BEGIN
        INSERT  dbo.T_Bllb_MSDResult_tbmr
                ( SerialNumber ,
                  Open_Time ,
                  Remark ,
                  Status
                )
        VALUES  ( '{0}' ,
                  GETDATE() ,
                  '{3}' ,
                  3
                )
    END
ELSE
    BEGIN
        UPDATE  T_Bllb_MSDResult_tbmr
        SET     Open_Time = GETDATE(),
		Expose_Time_Length=NULL,
		Close_Time=NULL
        WHERE   SerialNumber = '{0}'
    END
INSERT  dbo.T_Bllb_MSDDetail_tbmd
        ( SerialNumber ,
          Open_Time ,
          Remark ,
          Status
        )
VALUES  ( '{0}' ,
          GETDATE() ,
          '{3}' ,
          3
        )
    ", MSDMain_tbmm.SerialNumber, MSDMain_tbmm.Status, MSDMain_tbmm.MaterialCode, MSDMain_tbmm.Remark);

            sqlcmd += string.Format(@" UPDATE PUB_UniqueCodeInLine SET UnpackTime=GETDATE() WHERE UniqueCode='{0}'", MSDMain_tbmm.SerialNumber);
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
            return true;
        }
        /// <summary>
        /// 计算暴露时间
        /// </summary>
        public static void CalExposeTime(string serialnumber, out string msg)
        {
            DataTable dtSNInfo = Query(string.Format("AND a.SerialNumber='{0}' ", serialnumber));
            if (dtSNInfo.Rows.Count > 0)
            {
                DateTime _exposeTime = ConvertUtils.ChangeDateTimeToValue(dtSNInfo.Rows[0]["Open_Time"], DateTime.MinValue);
                if (_exposeTime == DateTime.MinValue)
                {
                    msg = "数据异常";
                    return;
                }
                else
                {
                    int _exposeLength = Convert.ToInt32(DateTime.Now.Subtract(_exposeTime).TotalMinutes);
                    if (_exposeLength + ConvertUtils.ChangeNullToInt(dtSNInfo.Rows[0]["LifeCycle"], 0) > (ConvertUtils.ChangeNullToInt(dtSNInfo.Rows[0]["ExposeTime"], 0) * 60))
                    {
                        msg = "实际暴露时间超过暴露时间需烘烤后才能密封";
                        return;
                    }
                    msg = "OK";
                    return;
                }
            }
            else
            {
                msg = "数据异常";
                return;
            }
        }
        /// <summary>
        /// 进行密封
        /// </summary>
        public static void Close_MSD(string serialnumber, out string msg, MSD_Operation_Enum _o_enum)
        {
            DataTable dtSNInfo = Query(string.Format("AND a.SerialNumber='{0}' ", serialnumber));
            if (dtSNInfo.Rows.Count == 0)
            {
                msg = "参数异常";
                return;
            }
            else
            {
                //MSD经过烘烤后在进行密封，MSD的生命周期会清零，未经过烘烤直接密封的MSD，生命周期暂停
                string sqlcmd = string.Empty;
                if (_o_enum == MSD_Operation_Enum.End_Bake)
                {//经过烘烤后密封
                    sqlcmd = string.Format(@"
DECLARE @mguid NVARCHAR(50)
SELECT TOP 1
        @mguid = Mguid
FROM    dbo.T_Bllb_MSDDetail_tbmd
WHERE   SerialNumber = '{0}'
ORDER BY Action_Time DESC
--更新追溯表
UPDATE  T_Bllb_MSDDetail_tbmd
SET     Close_Time = GETDATE() ,
        Status = '3'
WHERE   Mguid = @mguid
--更新记录表
UPDATE  dbo.T_Bllb_MSDResult_tbmr
SET     Close_Time = GETDATE() ,
        Expose_Time_Length=NULL,
        LifeCycle = 0 ,
        Status = '3'
WHERE   SerialNumber = '{0}'
--更新主表
UPDATE  dbo.T_Bllb_MSDMain_tbmm
SET     Status = '2'
WHERE   SerialNumber = '{0}'", serialnumber);
                }
                else
                {
                    //直接密封
                    sqlcmd = string.Format(@"
DECLARE @mguid NVARCHAR(50)
SELECT TOP 1
        @mguid = Mguid
FROM    dbo.T_Bllb_MSDDetail_tbmd
WHERE   SerialNumber = '{0}'
ORDER BY Action_Time DESC
--更新追溯表
UPDATE  T_Bllb_MSDDetail_tbmd
SET     Close_Time = GETDATE() ,
        Status = '3'
WHERE   Mguid = @mguid
--更新记录表
UPDATE  dbo.T_Bllb_MSDResult_tbmr
SET     Close_Time = GETDATE() ,
        LifeCycle = CAST(DATEDIFF(MINUTE, Open_Time, GETDATE()) AS INT)
        + CAST(ISNULL(LifeCycle, 0) AS INT) ,
        Expose_Time_Length=NULL,
        Status = '3'
WHERE   SerialNumber = '{0}'
--更新主表
UPDATE  dbo.T_Bllb_MSDMain_tbmm
SET     Status = '2'
WHERE   SerialNumber = '{0}'", serialnumber);
                }
                Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
                msg = "OK";
            }
        }
        /// <summary>
        /// 入烘烤箱(开始烘烤)
        /// </summary>
        /// <param name="serialnumber">条码</param>
        /// <param name="bake_orderNumner">烘烤序号</param>
        public static void IN_Baking(string serialnumber, string bake_orderNumner)
        {
            string sqlcmd = string.Format(@"
UPDATE dbo.T_Bllb_MSDMain_tbmm SET Status='5' WHERE SerialNumber='{0}'
INSERT dbo.T_Bllb_MSDDetail_tbmd
        ( SerialNumber ,
          Begin_Bake_Time ,
          Bake_OrderNumber ,
          Status ,
          Action_Time ,
          Mguid
        )
VALUES  ( N'{0}' , -- SerialNumber - nvarchar(50)
          GETDATE() , -- Begin_Bake_Time - datetime
		 '{1}' , -- Bake_OrderNumber - nvarchar(50)
          N'2' , -- Status - nvarchar(50)
          GETDATE() , -- Action_Time - datetime
          NEWID()  -- Mguid - nvarchar(50)
        )
UPDATE dbo.T_Bllb_MSDResult_tbmr SET Begin_Bake_Time=GETDATE(),Bake_OrderNumber='{1}',Status='2' WHERE SerialNumber='{0}'", serialnumber, bake_orderNumner);
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
        }
        /// <summary>
        /// 计算烘烤时长
        /// </summary>
        public static DataTable CalBakeTimeLength(string serialNumber)
        {
            string sqlcmd = string.Format(@"
DECLARE @begin_baking_time DATETIME ,
    @bake_time_length NVARCHAR(50)
IF NOT EXISTS ( SELECT  *
                FROM    dbo.T_Bllb_MSDResult_tbmr
                WHERE   SerialNumber = '{0}' )
    BEGIN
        SELECT  '0' ,
                'ReelID错误'
        RETURN 
    END
ELSE
    BEGIN
        SELECT  @begin_baking_time = Begin_Bake_Time ,
                @bake_time_length = Baking_Time_Length
        FROM    dbo.T_Bllb_MSDResult_tbmr
        WHERE   SerialNumber = '{0}'
        SET @bake_time_length = DATEDIFF(MINUTE, @begin_baking_time, GETDATE())
        UPDATE  T_Bllb_MSDResult_tbmr
        SET     Baking_Time_Length = @bake_time_length
        WHERE   SerialNumber = '{0}'
        SELECT  '1' ,
                @bake_time_length as 'bake_time_length'
        RETURN
    END
", serialNumber);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
        /// <summary>
        /// 出烘烤箱（结束烘烤）
        /// </summary>
        public static void Out_Baking(string serialNumber, string baking_time_length)
        {
            string sqlcmd = string.Format(@"
IF NOT EXISTS ( SELECT  *
                FROM    dbo.T_Bllb_MSDMain_tbmm
                WHERE   SerialNumber = '{0}' )
    BEGIN
        SELECT  '0' ,
                'ReelID错误'
        RETURN
    END
ELSE
    BEGIN
        UPDATE  dbo.T_Bllb_MSDMain_tbmm
        SET     Status = '6'
        WHERE   SerialNumber = '{0}'
        DECLARE @mguid NVARCHAR(50)
        SELECT TOP 1
                @mguid = Mguid
        FROM    T_Bllb_MSDDetail_tbmd
        ORDER BY Action_Time DESC       
        UPDATE  dbo.T_Bllb_MSDDetail_tbmd
        SET     Baking_Time_Length = '{1}' ,
                End_Bake_Time = GETDATE() ,
                Status = '3'
        WHERE   SerialNumber = '{0}'
                AND Mguid = @mguid
        UPDATE  dbo.T_Bllb_MSDResult_tbmr
        SET     Baking_Time_Length = '{1}' ,
                End_Bake_Time = GETDATE() ,
                Status = '3'
        WHERE   SerialNumber = '{0}'
        SELECT  '1' ,
                '结束烘烤成功'
        RETURN
    END

", serialNumber, baking_time_length);
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
        }
        /// <summary>
        /// 进干燥箱
        /// </summary>
        /// <param name="serialNumber">条码</param>
        /// <param name="mt_expose_time_length">维护的暴露时长</param>
        public static void IN_Drying(string serialNumber, string mt_expose_time_length)
        {
            string sqlcmd = string.Format(@"
IF NOT EXISTS ( SELECT  *
                FROM    dbo.T_Bllb_MSDResult_tbmr
                WHERE   SerialNumber = '{0}' )
    BEGIN
        SELECT  '0' ,
                'ReelID错误'
        RETURN 
    END
ELSE
    BEGIN
        DECLARE @act_expost_time_length NVARCHAR(50) --实际的暴露时长
        DECLARE @mt_expost_time_length NVARCHAR(50) --维护的暴露时长
        SELECT  @act_expost_time_length = Expose_Time_Length
        FROM    dbo.T_Bllb_MSDResult_tbmr
        WHERE   SerialNumber = '{0}'
        SET @mt_expost_time_length = '{1}'
		--如果实际暴露时间小于维护的暴露时间，进入干燥箱，生命周期清零
        IF ( CAST(ISNULL(@act_expost_time_length, 0) AS INT) < ( CAST(ISNULL(@mt_expost_time_length,
                                                              0) AS INT) * 60 ) )
            BEGIN
                UPDATE  dbo.T_Bllb_MSDResult_tbmr
                SET     Status = '1' ,
                        In_Drying_Time = GETDATE() ,
                        LifeCycle = 0
                WHERE   SerialNumber = '{0}'
            END
        ELSE
            IF ( CAST(ISNULL(@act_expost_time_length, 0) AS INT) >= ( CAST(ISNULL(@mt_expost_time_length,
                                                              0) AS INT) * 60 ) )	--大于等于生命周期暂停，进干燥箱.
                BEGIN
                    UPDATE  dbo.T_Bllb_MSDResult_tbmr
                    SET     Status = '1' ,
                            In_Drying_Time = GETDATE() ,
                            LifeCycle = CAST(DATEDIFF(MINUTE, Open_Time,
                                                      GETDATE()) AS INT)
                            + CAST(ISNULL(LifeCycle, 0) AS INT)
                    WHERE   SerialNumber = '{0}'
                END
        UPDATE  dbo.T_Bllb_MSDMain_tbmm
        SET     Status = '3'
        WHERE   SerialNumber = '{0}'
        INSERT  dbo.T_Bllb_MSDDetail_tbmd
                ( SerialNumber ,
                  In_Drying_Time ,
                  Status ,
                  Action_Time ,
                  Mguid
				 )
        VALUES  ( N'{0}' , -- SerialNumber - nvarchar(50)
                  GETDATE() , -- In_Drying_Time - datetime
                  N'1' , -- Status - nvarchar(50)
                  GETDATE() , -- Action_Time - datetime
                  NEWID()  -- Mguid - nvarchar(50)
				 )	         
    END
", serialNumber, mt_expose_time_length);
            Utils.NMS.Exectransql(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0], out flag, out result);
        }
        /// <summary>
        /// 出干燥箱
        /// </summary>
        /// <param name="serialNumber"></param>
        public static DataTable Out_Drying(string serialNumber)
        {
            string sqlcmd = string.Format(@"
IF NOT EXISTS ( SELECT  *
                FROM    dbo.T_Bllb_MSDMain_tbmm
                WHERE   SerialNumber = '{0}' )
    BEGIN
        SELECT  '0' ,
                'ReelID数据异常'
        RETURN
    END
ELSE
    BEGIN
        DECLARE @act_dry_time_length NVARCHAR(50) --实际干燥时长
        SELECT  @act_dry_time_length = DATEDIFF(MINUTE, In_Drying_Time,
                                                GETDATE())
        FROM    dbo.T_Bllb_MSDResult_tbmr
        WHERE   SerialNumber = '{0}'
        UPDATE  dbo.T_Bllb_MSDMain_tbmm
        SET     Status = '4'
        WHERE   SerialNumber = '{0}'
        DECLARE @mguid NVARCHAR(50)
        SELECT TOP 1
                @mguid = Mguid
        FROM    dbo.T_Bllb_MSDDetail_tbmd
        WHERE   SerialNumber = '{0}'
        ORDER BY Action_Time DESC
        UPDATE  dbo.T_Bllb_MSDDetail_tbmd
        SET     Status = '3' ,
                Action_Time = GETDATE() ,
                Out_Dtying_Time = GETDATE()
        WHERE   Mguid = @mguid
        UPDATE  dbo.T_Bllb_MSDResult_tbmr
        SET     Status = '3' ,
                Out_Dtying_Time = GETDATE() ,
                Dry_time_length = @act_dry_time_length
        WHERE   SerialNumber = '{0}'
        SELECT  '1' ,
                'OK'
        RETURN 
    END          
", serialNumber);
            return Utils.NMS.QueryDataTable(Utils.uContext, sqlcmd, new CIT.WMS.MESWcf.CmdParameter[0]);
        }
    }
}
